package com.infore.siteManagement.repository;

import java.util.List;
import java.util.Map;

import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import com.infore.siteManagement.entity.ProjectAndEquipment;

@Repository
public interface IProjectAndEquipmentRepository extends JpaRepository<ProjectAndEquipment, String>,JpaSpecificationExecutor<ProjectAndEquipment>{

	//仪器详细信息查询
	@Query(value = "select p.project_name,e.equipment_name,e.manufactor,t.reagent,t.detection_limit,t.upper_limit,t.lower_limit,p.project_unit,e.manufacture_code,e.create_time, (case e.isenable when '1' then '是' else '否' end ) as isenable,t.id,t.analy_methons,e.equipment_model,e.equipment_orange,p.PROJECT_CODE,e.equipment_id " + 
			"from PROJECT_AND_EQUIPMENT t left join EQUIPMENT e on t.equipment_id = e.equipment_id left join PROJECT p on t.project_id = p.project_id " + 
			"where t.equipment_id in (:eqList)", nativeQuery = true)
	List<Map<String, Object>> selectEquipmentAndProject(@Param("eqList") List<String> eqList);
	
	//仪器检测项目 量程 分析方法 生产厂商 型号
	@Query(value = "select p.project_name,e.manufactor,t.analy_methons,e.equipment_model,e.equipment_orange " + 
			"from PROJECT_AND_EQUIPMENT t left join EQUIPMENT e on t.equipment_id = e.equipment_id left join PROJECT p on t.project_id = p.project_id " + 
			"where t.equipment_id in (:eqList)", nativeQuery = true)
	List<Map<String, Object>> selectEquipmentFive(@Param("eqList") List<String> eqList);

	ProjectAndEquipment findOneById(String id);
	
	/**通过因子删除中间表**/
	@Modifying
	@Transactional
	int deleteByprojectIdIn(List<String> ids);
}
